package com.shujia.spark.sql

import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}

object Demo2DataSource {

  def main(args: Array[String]): Unit = {

    val spark = SparkSession
      .builder
      .appName("datasource")
      .master("local")
      .getOrCreate()

    /**
      *
      * 读取json格式的数据
      * json 中自带列名和列的类型的，spark 在读取的过程中会自动解析
      *
      */

    val studentJson: DataFrame = spark
      .read
      .format("json")
      .load("data/students.json")
      //.select("id","name","age","gender","clazz")
      //.show()

    /**
      * 读取csv格式的文件
      */

    val csvDF: DataFrame = spark
      .read
      .format("csv")
      .option("sep",",")
      .schema("id STRING,name STRING,age INT,gender STRING,clazz STRING")
      .load("data/csv")

    //csvDF.show()
    csvDF.printSchema()

    /**
      * 保存为parquet格式
      *
      */

    csvDF.write.mode(SaveMode.Overwrite).parquet("data/parquet")


    /**
      *
      * 读取parquet格式的数据
      * 1、自带列名
      * 2、数据会压缩 (压缩和解压需要时间，压缩比一般在5倍左右)
      * 3、可以兼容hive
      */


    val parquetDF: DataFrame = spark
      .read
      .format("parquet")
      .load("data/parquet")


    //parquetDF.printSchema()
    //parquetDF.show()

    //csvDF.write.mode(SaveMode.Overwrite).orc("data/orc")

    /**
      * 读取orc格式
      * 1、自带列名
      * 2、数据会压缩 (压缩和解压需要时间，压缩比一般在5倍左右)
      * 3、可以兼容hive
      */


    val orcDF: DataFrame = spark
      .read
      .format("orc")
      .load("data/orc")

    //orcDF.show()
    //orcDF.printSchema()

    /**
      * jdbc读取数据
      */

    val jdbcDF: DataFrame = spark
      .read
      .format("jdbc")
      .option("url","jdbc:mysql://localhost:3306")
      //.option("dbtable","students.student")
      .option("query","select * from students.student where gender='女'")  //和dbtable只能存在一个
      .option("user","root")
      .option("password","root")
      .load()
    //jdbcDF.show()

    //jdbcDF.printSchema()

    orcDF.select("name","age","gender","clazz")
      .write
      .format("jdbc")
      .mode(SaveMode.Overwrite)
      .option("url","jdbc:mysql://localhost:3306")
      .option("dbtable","students.student3")
      .option("user","root")
      .option("password","root")
      //手动指定列的类型
      .option("createTableColumnTypes","name varchar(20),age int,gender varchar(2),clazz varchar(10)")
      //.option("customSchema","name varchar(20),age int,gender varchar(2),clazz varchar(10)")
      .save()

  }

}
